package br.unirio.ticketnator.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//import java.sql.Types;
import java.sql.SQLException;
//import java.sql.CallableStatement;
import java.util.List;
import java.util.ArrayList;

import br.unirio.ticketnator.model.Usuario; 

public class UsuarioDAO implements IUsuarioDAO {
	
	/**
	 * Carrega os dados de um Usuario a partir de uma consulta
	 */
	
	private Usuario carrega(ResultSet rs) throws SQLException
	{
		Usuario user = new Usuario();
		user.setIdUsuario(rs.getInt("id_usuario"));
		user.setCodPermissao(rs.getInt("codigo_tipo_usuario"));
		user.setNome(rs.getString("nome_usuario"));
		user.setSobrenome(rs.getString("sobrenome"));
		user.setEmail(rs.getString("email"));
		user.setSenha(rs.getString("senha"));
		
		return user;
	}
	
	@Override
	public Usuario getUsuario(int id) {
		Connection c = SupportDAO.getConnection();
		
		if(c == null)
			return null;
		
		Usuario user = null;
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT * FROM TB_USUARIO WHERE ID_USUARIO = ?");
			ps.setLong(1, id);
			
			ResultSet rs = ps.executeQuery();
			
			if (rs.next())
				user = carrega(rs);
			
			c.close();	
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return user;
	}
	
        @Override
	public Usuario getUsuario(String email) {
		Connection c = SupportDAO.getConnection();
		
		if(c == null)
			return null;
		
		Usuario user = null;
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT * FROM TB_USUARIO WHERE EMAIL = ?");
			ps.setString(1, email);
			
			ResultSet rs = ps.executeQuery();
			
			if (rs.next())
				user = carrega(rs);
			
			c.close();	
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return user;
	}
        
        
	@Override
	public int conta() {
		Connection c = SupportDAO.getConnection();
		
		if (c == null)
			return 0;
		
		int numero = 0;
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT COUNT (*) FROM TB_USUARIO");
			ResultSet rs = ps.executeQuery();
			
			if (rs.next())
				numero = rs.getInt(1);
			
			c.close();
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return numero;
	}
	
	@Override
	public List<Usuario> lista(int pagina, int tamanho)
	{
		Connection c = SupportDAO.getConnection ();
		
		if (c == null)
			return null;
		
		List<Usuario> lista = new ArrayList<Usuario>();
		
		try
		{
			PreparedStatement ps = c.prepareStatement("SELECT * FROM TB_USUARIO ORDER BY NOME_USUARIO LIMIT ? OFFSET ? ");
			ps.setInt(1, tamanho);
			ps.setInt(2, pagina * tamanho);
			ResultSet rs = ps.executeQuery();
			
			while (rs.next())
				lista.add(carrega(rs));
			
			c.close();
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
		}
		
		return lista;
	}
	
	@Override
	public boolean insere(Usuario user) 
	{
		Connection c = SupportDAO.getConnection();
		String insertUser = "INSERT INTO TB_USUARIO"
				       +"(NOME_USUARIO, SOBRENOME, EMAIL, SENHA, CODIGO_TIPO_USUARIO) VALUES"
				       +"(?,?,?,?,?)";
		
		if (c == null)
			return false;
		try
		{
//			CallableStatement cs = c.prepareCall("{call InsereUsuario(?, ?, ?, ?, ?)}");
//			cs.setInt(1, user.getCodPermissao());
//			cs.setString(2, user.getNome());
//			cs.setString(3, user.getSobrenome());
//			cs.setString(4, user.getEmail());
//			cs.setString(5, user.getSenha());
//			cs.registerOutParameter(6, Types.INTEGER);
//			
//			int id = cs.getInt(6);
//			user.setIdUsuario(id);
//			cs.execute();
			
			PreparedStatement ps = c.prepareStatement(insertUser);
			ps.setString(1, user.getNome());
			ps.setString(2, user.getSobrenome());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getSenha());
			ps.setInt(5, user.getCodPermissao());
			ps.executeUpdate();
			c.close();
			
			return true;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
    			return false;
		}	
	}
	
	@Override
	public boolean atualiza(Usuario user) 
	{
		Connection c = SupportDAO.getConnection();
		String updateUser = "UPDATE TB_USUARIO"
				       +"SET NOME_USUARIO = ?"
				       +"SOBRENOME = ?"
				       +"EMAIL = ?"
				       +"SENHA = ?"
				       +"CODIGO_TIPO_USUARIO = ?"
				       +"WHERE ID_USUARIO = ?";
		
		if ( c == null)
			return false;
		
		try
		{
//			CallableStatement cs = c.prepareCall("{call AtualizaUsuario(?, ?, ?, ?, ?, ?)}");
//			cs.setInt(1, user.getIdUsuario());
//			cs.setInt(2, user.getCodPermissao());
//			cs.setString(3, user.getNome());
//			cs.setString(4, user.getSobrenome());
//			cs.setString(5, user.getEmail());
//			cs.setString(6, user.getSenha());
//			cs.execute();
			
			PreparedStatement ps = c.prepareStatement(updateUser);
			ps.setString(1, user.getNome());
			ps.setString(2, user.getSobrenome());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getSenha());
			ps.setInt(5, user.getCodPermissao());
			ps.setInt(6, user.getIdUsuario());
			ps.executeUpdate();
			c.close();
			
			return true;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return false;
		}
	}
	
	@Override
	public boolean remove(int id) 
	{
		Connection c = SupportDAO.getConnection();
		String deleteUser = "DELETE FROM TB_USUARIO WHERE ID_USUARIO = ?";
		
		if (c == null)
			return false;
		
		try
		{
//			CallableStatement cs = c.prepareCall("{call RemoveUsuario(?)}");
//			cs.setInt(1,id);
//			cs.execute();
			
			PreparedStatement ps = c.prepareStatement(deleteUser);
			ps.setInt(1, id);
			ps.executeUpdate();
			c.close();
			
			return true;
			
		} catch (SQLException e)
		{
			SupportDAO.log(e.getMessage());
			return false;
		}
	}
       
}
